package com.carlinfo.common.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;


public class DbUtil
{
	/**
	 * 在本类New对象定为private类型，实现单例模式
	 */
	private static DbUtil INSTANCE = new DbUtil();
	/**
	 * 定义driver的包名加类名
	 */
	private String driver = ConstantFinalUtil.bundle.getString("jdbc.driver");
	private String url = ConstantFinalUtil.bundle.getString("jdbc.url");
	private String user = ConstantFinalUtil.bundle.getString("jdbc.user");
	private String password=ConstantFinalUtil.bundle.getString("jdbc.password");
	/**
	 * 私有的构造方法 并实现加载驱动的代码
	 */
	private DbUtil()
	{
		try
		{
			Class.forName(driver);
		} catch (ClassNotFoundException e)
		{
			System.out.println("加载驱动失败");
			e.printStackTrace();
		}
	}
	public static DbUtil getInstance()
	{
		return INSTANCE;
	}
	/**
	 * 获取链接
	 * @return
	 */
	public Connection getconn() 
	{
		try
		{
			Connection connection = DriverManager.getConnection(url, user, password);
			return connection;
		} catch (SQLException e)
		{
			System.out.println("获取连接失败");
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 关闭连接
	 */
	public void closeAll(Connection conn,Statement pre,ResultSet res)
	{
		try
		{
			if(res != null)
			{
				res.close();
				res = null;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		try
		{
			if(pre != null)
			{
				pre.close();
				pre = null;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		try
		{
			if(conn != null)
			{
				conn.close();
				conn = null;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
	public void standardJDBC()
	{	
		/**
		 * 加载驱动
		 */
		String driver = "com.mysql.jdbc.Driver";
		ResultSet res = null;
		PreparedStatement pre = null;
		Connection conn = null;
		try
		{
			Class.forName(driver);
			String url = "jdbc:mysql:///userscenter?useSSL=false";
			String user = "root";
			String password = "123456";
			String sql = "select * from a_dynasty";
			conn = DriverManager.getConnection(url, user, password);
			pre = conn.prepareStatement(sql);
			res = pre.executeQuery();
			System.out.println("id\t名字\t首都\t国祚\t状态\t开国时间\t\t\t创建时间\t\t\t更新时间\t\t\t发布时间");
			while(res.next())
			{
				int id = res.getInt("id");
				String name = res.getString("name");
				String capital = res.getString("capital");
				int age = res.getInt("age");
				Date st = res.getDate("st");
				short status = res.getShort("status");
				Date createTime = res.getTimestamp("createTime");
				Date updateTime = res.getTimestamp("updateTime");
				Date pubTime = res.getTimestamp("pubTime");
				System.out.println(id + "\t" + name + "\t" + capital + "\t"
						+ age + "\t" + status + "\t" + st.toLocaleString() + "\t"
						+ createTime.toLocaleString() + "\t" + updateTime.toLocaleString() 
						+ "\t" + pubTime.toLocaleString());
			}
		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally 
		{
			try
			{
				if(res != null)
				{
					res.close();
					res = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(pre != null)
				{
					pre.close();
					pre = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
			try
			{
				if(conn != null)
				{
					conn.close();
					conn = null;
				}
			} catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
	}
	public void NewJDBC() 
	{
		String sql = "select * from a_dynasty";
		DbUtil dbutil = DbUtil.getInstance();
		Connection conn = dbutil.getconn();
		PreparedStatement pre = null;
		ResultSet res = null;
		try
		{
			pre = conn.prepareStatement(sql);
			res = pre.executeQuery();
			while(res.next())
			{
				int id = res.getInt("id");
				String name = res.getString("name");
				String capital = res.getString("capital");
				int age = res.getInt("age");
				Date st = res.getDate("st");
				short status = res.getShort("status");
				Date createTime = res.getTimestamp("createTime");
				Date updateTime = res.getTimestamp("updateTime");
				Date pubTime = res.getTimestamp("pubTime");
				System.out.println(id + "\t" + name + "\t" + capital + "\t"
						+ age + "\t" + status + "\t" + st.toLocaleString() + "\t"
						+ createTime.toLocaleString() + "\t" + updateTime.toLocaleString() 
						+ "\t" + pubTime.toLocaleString());
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally 
		{
			dbutil.closeAll(conn, pre, res);
		}
	}
	public static void main(String[] args)
	{
		System.out.println("====");
		DbUtil dbutil = DbUtil.getInstance();
//		dbutil.standardJDBC();
		dbutil.NewJDBC();
	}
}
